package cn.com.libertymutual.sp.service.impl;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaBuilder.In;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPClientConfig;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.client.RestTemplate;

import com.alibaba.fastjson.JSONObject;
import com.beust.jcommander.internal.Lists;
import com.google.common.collect.ImmutableList;

import cn.com.libertymutual.core.email.IEmailService;
import cn.com.libertymutual.core.redis.util.RedisUtils;
import cn.com.libertymutual.core.sftp.SFtpClient;
import cn.com.libertymutual.core.util.CommonUtil;
import cn.com.libertymutual.core.util.Constants;
import cn.com.libertymutual.core.util.DateUtil;
import cn.com.libertymutual.core.util.StringUtil;
import cn.com.libertymutual.core.util.enums.CoreServiceEnum;
import cn.com.libertymutual.core.web.ServiceResult;
import cn.com.libertymutual.core.web.util.RequestUtils;
import cn.com.libertymutual.core.web.util.ResponseUtils;
import cn.com.libertymutual.sp.bean.TbSpApplicant;
import cn.com.libertymutual.sp.bean.TbSpOrder;
import cn.com.libertymutual.sp.bean.TbSpScoreLogIn;
import cn.com.libertymutual.sp.bean.TbSpUser;
import cn.com.libertymutual.sp.bean.TbSysHotarea;
import cn.com.libertymutual.sp.dao.ApplicantDao;
import cn.com.libertymutual.sp.dao.OrderDao;
import cn.com.libertymutual.sp.dao.ScoreLogInDao;
import cn.com.libertymutual.sp.dao.TbSysHotareaDao;
import cn.com.libertymutual.sp.dao.UserDao;
import cn.com.libertymutual.sp.dto.CommonRequestDto;
import cn.com.libertymutual.sp.dto.TDownFileRequestDto;
import cn.com.libertymutual.sp.dto.TDownFileResponseDto;
import cn.com.libertymutual.sp.service.api.CaptchaService;
import cn.com.libertymutual.sp.service.api.IOrderService;
import cn.com.libertymutual.sp.service.api.ScoreService;
import cn.com.libertymutual.sp.webService.einv.EinvoicePrintRequestDto;
import cn.com.libertymutual.sp.webService.einv.EinvoicePrintResponseDto;
import cn.com.libertymutual.sys.bean.SysBranch;
import cn.com.libertymutual.sys.bean.SysServiceInfo;
import cn.com.libertymutual.sys.dao.SysBranchDao;

@Service("IOrderService")
public class OrderServiceImpl implements IOrderService {
	private Logger log = LoggerFactory.getLogger(getClass());
	@Resource
	private RedisTemplate<String, Object> redisTemplateObj;
	@Resource
	private RestTemplate restTemplate;
	@Resource
	private SFtpClient sftpClient;
	@Autowired
	private SysBranchDao sysBranchDao;
	@Resource
	private RedisUtils redisUtils;
	@Resource
	private CaptchaService captchaService;
	@Resource
	private IEmailService emailService;
	@Autowired
	private ApplicantDao applicantDao;
	/*
	 * @Autowired private KeywordDao keywordDao;
	 * 
	 * private final String PRINT_PREFIX = "printData:"; private final String
	 * FILE_TYPE_POLICY = "P"; private final String POLICY_FILE_DOWN_PRE =
	 * "EPolicyFile_"; private final int PRINT_FILE_CACHE_TIME = 60 * 15; // 15分钟后失效
	 */
	private final String ResultCode_0001 = "0001";
	private final String ENCODING_UTF_8 = "UTF-8";
	@Autowired
	private OrderDao orderDao;
	@Autowired
	private UserDao userDao;
	@Autowired
	private ScoreLogInDao scoreLogInDao;
	@Autowired
	private TbSysHotareaDao tbSysHotareaDao;
	@Autowired
	private LevelServiceImpl levelService;
	/*
	 * @Autowired private ShopService shopService;
	 */
	@Autowired
	private ScoreService scoreService;
	@Value("${plan.request.consumerid}")
	private String consumerId;

	@Value("${plan.request.operatecode}")
	private String operateCode;

	@Value("${plan.request.usercode}")
	private String userCode;

	@Value("${plan.request.password}")
	private String passWord;

	@Autowired
	private JdbcTemplate readJdbcTemplate;

	/*
	 * @Autowired private NamedParameterJdbcTemplate readJdbcTemplate2;
	 */

	@Override
	public ServiceResult sendEinvPrintData(EinvoicePrintRequestDto einvPrintData) {
		SysServiceInfo sysServiceInfo = null;
		ServiceResult result = new ServiceResult();
		// 获取地址，如果错误则直接抛出异常
		try {
			Map map = (Map) redisUtils.get(Constants.SYS_SERVICE_INFO);
			sysServiceInfo = (SysServiceInfo) map.get(CoreServiceEnum.Print_Eletronic_Invoice.getUrlKey());
			// sysServiceInfo.setUrl("http://10.132.30.109:7001/prpall/EinvoicePrintServlet");
			einvPrintData.setIsMotorUi("SalePlate"); // 设置数据来源
			/*
			 * einvPrintData.setEmailAddress("kfwang@isofstone.com");
			 * einvPrintData.setCertiNo("8805025000170000194000");
			 * einvPrintData.setInvoiceTitle("软通动力");
			 * einvPrintData.setTaxPayerno("32432423");
			 */
			HttpHeaders headers = new HttpHeaders();
			headers.setContentType(MediaType.APPLICATION_XML);
			HttpEntity<EinvoicePrintRequestDto> requestEntity = new HttpEntity<EinvoicePrintRequestDto>(einvPrintData, headers);
			ResponseEntity<EinvoicePrintResponseDto> einvPrintRsgDataResp = restTemplate.exchange(sysServiceInfo.getUrl(), HttpMethod.POST,
					requestEntity, EinvoicePrintResponseDto.class);
			EinvoicePrintResponseDto einvPrintRsgData = einvPrintRsgDataResp.getBody();
			result.setResult(einvPrintRsgData);
			result.setResCode(einvPrintRsgData.getErrorCode());
			if (einvPrintRsgData.getErrorCode().equals("000")) {
				result.setResult(einvPrintRsgData);
				result.setSuccess();
			} else {
				result.setResult(einvPrintRsgData.getErrorMsg());
				result.setFail();
			}

			// 未能获取到正确的连接地址
		} catch (Exception e) {
			result.setAppFail();
			log.error(e.getMessage());
			result.setResult(e.getMessage());
			return result;
		}
		return result;
	}

	@Override
	public ServiceResult queryEinv(String policyNo, String identifyNo) {
		SysServiceInfo sysServiceInfo = null;
		ServiceResult result = new ServiceResult();
		result.setFail();
		URL url = null;
		HttpURLConnection hcn = null;
		OutputStream output = null;
		BufferedReader bReader = null;
		Document document = null;
		// policyNo="8805025000170000194000";
		// identifyNo="513029198706020698";
		// 获取地址，如果错误则直接抛出异常
		try {
			Map map = (Map) redisUtils.get(Constants.SYS_SERVICE_INFO);
			sysServiceInfo = (SysServiceInfo) map.get(CoreServiceEnum.Query_Eletronic_Invoice.getUrlKey());
			// sysServiceInfo.setUrl("http://10.132.30.109:7001/prpall/EinvoicePrintServlet");
			url = new URL(sysServiceInfo.getUrl());
			hcn = (HttpURLConnection) url.openConnection();
			// 设置连接参数
			hcn.setRequestMethod("POST");
			hcn.setDoOutput(true);
			hcn.setDoInput(true);
			hcn.setAllowUserInteraction(true);

			String reqestXml = eipRequestXml(policyNo, identifyNo);
			log.info("查询接口请求,参数:[ 保单号: " + policyNo + ",证件号: " + identifyNo + " ],reqestXml: " + reqestXml);

			// 开启流，写入XML数据
			output = hcn.getOutputStream();
			// System.out.println("发送的报文：");
			// System.out.println(reqestXml);

			output.write(reqestXml.getBytes(ENCODING_UTF_8));
			output.flush();

			// 接收xml
			bReader = new BufferedReader(new InputStreamReader(hcn.getInputStream(), ENCODING_UTF_8));
			String line = null;
			StringBuilder sb = new StringBuilder();
			while ((line = bReader.readLine()) != null) {
				sb.append(line);
				// System.out.println("xml::::" + sb.toString());
			}
			log.info("查询接口返回,参数:[ 保单号: " + policyNo + ",证件号: " + identifyNo + " ],responseXml: " + sb.toString());
			document = DocumentHelper.parseText(sb.toString());
			Element root_ReturnData = document.getRootElement();// 得到根节点
			Map<String, Object> elementMap = new HashMap<String, Object>();
			getElementMap(root_ReturnData, elementMap);
			String code = elementMap.get("ErrorCode").toString().trim();
			if ("000".equals(code)) { // 正常
				result.setResCode(elementMap.get("PrintFlag").toString());// 开票状态
																			// 0未开，1已开，-1红冲
				result.setResult(elementMap);
				result.setSuccess();
			} else {
				result.setResCode(code);
				result.setResult(elementMap.get("ErrorDesc").toString());
			}

			// 未能获取到正确的连接地址
		} catch (Exception e) {
			e.printStackTrace();
			result.setAppFail();
			log.error(e.getMessage());
			result.setResult(e.getMessage());
			return result;
		}
		return result;
	}

	@Override
	public ServiceResult createEPolicy(String documentNo) {
		ServiceResult sr = new ServiceResult();
		try {
			// 调用生成接口
			Map map = (Map) redisUtils.get(Constants.SYS_SERVICE_INFO);
			SysServiceInfo sysServiceInfo = (SysServiceInfo) map.get(CoreServiceEnum.COMMON_REQUEST.getUrlKey());
			CommonRequestDto commonRequestDto = new CommonRequestDto();
			commonRequestDto.setPartnerAccountCode(sysServiceInfo.getUserName());
			String flowId = commonRequestDto.getPartnerAccountCode() + new Date().getTime();
			JSONObject obj = new JSONObject();
			obj.put("businessNo", documentNo);
			obj.put("consumerId", consumerId);
			obj.put("usercode", userCode);
			obj.put("operateCode", operateCode);
			obj.put("password", passWord);
			commonRequestDto.setFlowId(flowId);
			commonRequestDto.setRequestJson(obj.toJSONString());
			commonRequestDto.setRequestType("REPRINT_FTP");
			/*
			 * HttpHeaders headers = new HttpHeaders(); headers.add("account-code",
			 * sysServiceInfo.getUserName()); headers.add("request-time", String.valueOf(new
			 * Date().getTime())); headers.add("soa-token",
			 * pwdEncoder.encodePassword(sysServiceInfo.getPassword(), String.valueOf(new
			 * Date().getTime())));
			 */

			HttpEntity<CommonRequestDto> requestEntity = new HttpEntity<CommonRequestDto>(commonRequestDto,
					RequestUtils.genEncryptHttpHeaders(sysServiceInfo.getUserName(), sysServiceInfo.getPassword()));
			sr = restTemplate.postForObject(sysServiceInfo.getUrl(), requestEntity, ServiceResult.class);
			log.info("调用生成电子保单接口成功：" + obj.toJSONString());
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			e.printStackTrace();
		}
		return sr;
	}

	@Override
	public ServiceResult downPolicy(String documentNo, String proposalDate, String emailStr, String applicantName, HttpServletResponse response) {
		// this.createEPolicy("8131051100170100051000");
		// documentNo = "8805025008170000042000";
		// proposalDate = "2017-11-11";
		log.info("下载文件:{}", documentNo);
		ServiceResult result = inDownPolicy(documentNo, proposalDate, emailStr, applicantName, response);
		if (!result.isSuccess()) {// 未生成文件
			log.info("下载文件:{} 失败，重新生成", documentNo);
			new Thread() {
				public void run() {
					createEPolicy(documentNo);
					int count = 0;
					boolean flag = false;
					while (!flag && count < 5) {
						try {
							Thread.sleep(5000);
						} catch (InterruptedException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						ServiceResult result = inDownPolicy(documentNo, proposalDate, emailStr, applicantName, response);// 再次打印
						flag = result.isSuccess();
						count = count + 1;
						log.info("{}多线程读取ftp文件：{}次", count, documentNo);
					}
				}
			}.start();

		}
		return result;
	}

	/**
	 * 下载
	 * 
	 * @param documentNo
	 * @param proposalDate
	 * @param emailStr
	 * @param applicantName
	 * @param response
	 * @return
	 */
	private ServiceResult inDownPolicy(String documentNo, String proposalDate, String emailStr, String applicantName, HttpServletResponse response) {
		ServiceResult result = new ServiceResult();
		result.setFail();
		result.setResult("你的请求已受理,请稍后查收您的邮箱！");
		// documentNo = "8805025008170000042000";
		// proposalDate = "2017-10-11";
		/// ChannelSftp channelSftp2 = null;
		if (StringUtil.isEmpty(documentNo)) {
			result.setFail();
			result.setResult("保单号不能为空！");
			return result;
		}

		FTPClient ftpClient = null;

		try {

			@SuppressWarnings("unchecked")
			Map<String, SysServiceInfo> map = (Map<String, SysServiceInfo>) redisUtils.get(Constants.SYS_SERVICE_INFO);
			SysServiceInfo sysServiceInfo = map.get(CoreServiceEnum.NomEPolicyDown_URL.getUrlKey());
			if (null == sysServiceInfo) {
				result.setFail();
				result.setResult("未获取到下载地址信息，请进行配置！");
				return result;
			}
			String riskCode = documentNo.substring(2, 6);

			ftpClient = new FTPClient();
			// String host = "124.127.203.229";//外网
			String host = sysServiceInfo.getUrl();// 10.132.1.203
			String user = sysServiceInfo.getUserName();// dzbd
			int port = 9021;// 9021

			if (!StringUtils.isBlank(host) && host.lastIndexOf(":") > 1) {
				port = Integer.parseInt(host.substring(host.lastIndexOf(":") + 1));
				host = host.substring(0, host.indexOf(":"));
			}
			String pwd = sysServiceInfo.getPassword();// !qAz@19%
			// StringBuilder dirBu = new
			// StringBuilder(File.separator).append(sysServiceInfo.getTocken());
			// if(!StringUtils.isBlank(sysServiceInfo.getTocken())){
			// dirBu.append(File.separator);
			// }
			// String
			// dir=dirBu.append(riskCode).append(File.separator).append(proposalDate).append(File.separator).toString();
			if (StringUtils.isBlank(proposalDate)) {
				List<TbSpOrder> sbOrder = orderDao.findByPolicyNo(documentNo);
				if (sbOrder.size() == 0) {
					result.setFail();
					result.setResult("未查询到电子保单，请联系出单渠道商！");
				} else {
					proposalDate = DateUtil.dateFromat(sbOrder.get(0).getCreateTime());
				}
			}

			String dir = new StringBuilder(sysServiceInfo.getTocken()).append(File.separator).append(riskCode).append(File.separator)
					.append(proposalDate).append(File.separator).toString();
			ftpClient.connect(host, port);
			ftpClient.login(user, pwd);
			int reply = ftpClient.getReplyCode();
			if (!FTPReply.isPositiveCompletion(reply)) {
				ftpClient.disconnect();

			}
			ftpClient.setBufferSize(1024);
			// 设置文件类型（二进制）
			ftpClient.setFileType(FTPClient.BINARY_FILE_TYPE);
			FTPClientConfig conf = new FTPClientConfig(FTPClientConfig.SYST_UNIX);
			ftpClient.configure(conf);

			// 设置访问被动模式
			ftpClient.setRemoteVerificationEnabled(false);
			ftpClient.enterLocalPassiveMode();

			String tempFilePath = System.getProperty("java.io.tmpdir") + File.separator + "policy" + File.separator;
			if (!new File(tempFilePath).exists()) {
				new File(tempFilePath).mkdir();
			}
			boolean flag = ftpClient.changeWorkingDirectory(dir);
			File localFile = null;
			String fileName = "";
			if (!flag) {
				result.setResCode(ResultCode_0001);
				return result;
			} else {
				FTPFile[] fs = ftpClient.listFiles();
				if (null == fs || fs.length == 0) {
					result.setResCode(ResultCode_0001);
					return result;
				}
				for (FTPFile f : fs) {
					fileName = f.getName();
					if (fileName.equals(documentNo + ".pdf")) {

						if (StringUtil.isNotEmpty(emailStr)) {// 发送邮件

							localFile = new File(tempFilePath, f.getName());
							OutputStream ios = new FileOutputStream(localFile);
							ftpClient.retrieveFile(f.getName(), ios);
							ios.close();

							/*
							 * List<String> paths = Lists.newArrayList(); paths.add(tempFilePath);
							 * List<String> emailList = Lists.newArrayList(); emailList.add(emailStr);
							 * emailService.sendEmail("利宝保险电子保单",
							 * CommonUtil.fetchSendMessage(applicantName), emailList, paths);
							 */
							log.info("{}电子保单发送{}", emailStr, localFile.getAbsolutePath());
							emailService.sendEmail("利宝保险电子保单", CommonUtil.fetchSendMessage(applicantName), ImmutableList.of(emailStr), null, null,
									localFile, true);

							localFile.delete();
						} else {// 下载

							/*
							 * byte[] data = FileUtils.readFileToByteArray(localFile); ByteArrayInputStream
							 * input; input = new ByteArrayInputStream(data); // 修改流的类型为pdf //
							 * response.setContentType("application/pdf"); //
							 * response.setHeader("Content-type", "application/pdf");
							 * response.setContentType("application/octet-stream");
							 * response.setHeader("Content-type", "application/octet-stream");
							 * response.setHeader("Content-disposition", "attachment;filename=" + fileName);
							 * // response.setCharacterEncoding("GBK"); ///// fileName = new
							 * String(fileName.getBytes("GBK"), ///// "ISO8859-1"); // 修改流的类型为pdf
							 * 
							 * // 这个的添加也是因为影响到了打印 response.setHeader("Accept-Ranges", "bytes");
							 * 
							 * response.setHeader("Pragma", "No-cache"); response.setHeader("Cache-Control",
							 * "no-cache"); // response.setHeader("Content-type", //
							 * "application/octet-stream"); response.setContentLength(input.available());
							 * OutputStream os = response.getOutputStream(); InputStream input2 = null;
							 * ResponseUtils.steam(input2, os);
							 */

							log.info("{}", f.getSize());

							ResponseUtils.download(f.getName(), ftpClient.retrieveFileStream(f.getName()), response);

						}
						result.setSuccess();

						break;
					}
				}
			}

		} catch (IOException e) {
			// TODO Auto-generated catch block
			// log.error(e);
			log.error("读取保单文件异常", e);
			result.setAppFail();
			result.setResult("读取保单文件异常,请联系管理员");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			// log.error(e);
			log.error("发送保单文件异常", e);
			result.setAppFail();
			result.setResult("读取保单文件错误,请联系管理员");
		} finally {
			if (null != ftpClient && ftpClient.isConnected()) {
				try {
					ftpClient.disconnect();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	@Override
	public ServiceResult getOrderList(String sortfield, String sortfieldData, String sorttype, String sortData, int pageNumber, int pageSize,
			String status) {

		ServiceResult sr = new ServiceResult();
		Sort sort = null;
		if ("ASC".equals(sorttype)) {
			sort = new Sort(Direction.ASC, sortData);
		} else {
			sort = new Sort(Direction.DESC, sortData);
		}
		SetInvalidOrder(sortfieldData);
		Page<TbSpOrder> page = orderDao.findAll(new Specification<TbSpOrder>() {
			public Predicate toPredicate(Root<TbSpOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> predicate = new ArrayList<Predicate>();

				String userCodeBs = "-";// -表示用户的userCode值在订单的userId字段不可能有该字符
				TbSpUser user = userDao.findByUserCodeAll(sortfieldData);
				if (user != null && StringUtils.isNotBlank(user.getUserCodeBs())) {
					userCodeBs = user.getUserCodeBs();
					log.info("合并的用户[id={},code={}],有codebs值={}", user.getId(), user.getUserCode(), user.getUserCodeBs());
				}
				predicate.add(cb.or(cb.equal(root.get(sortfield).as(String.class), sortfieldData),
						cb.equal(root.get(sortfield).as(String.class), userCodeBs)));

				if (StringUtils.isNotEmpty(status)) {
					log.info(status);
					predicate.add(cb.equal(root.get("status").as(String.class), status));
				}
				Predicate[] pre = new Predicate[predicate.size()];
				return query.where(predicate.toArray(pre)).getRestriction();
			}
		}, PageRequest.of(pageNumber - 1, pageSize, sort));

		List<TbSpOrder> orders = page.getContent();
		Map<String, TbSpOrder> mapOrder = new HashMap<String, TbSpOrder>();
		for (TbSpOrder order : orders) {

			// 合并关联订单
			if (StringUtils.isNotBlank(order.getRelationId())
					&& (TbSpOrder.STATUS_NO_PAY.equals(order.getStatus()) || TbSpOrder.STATUS_UNDERWRITING.equals(order.getStatus()))) {
				TbSpOrder reOrder = mapOrder.get(order.getRelationId());
				if (reOrder == null) {
					mapOrder.put(order.getRelationId(), order);
					List<TbSpOrder> reOrders = orderDao.findRelationOrder(order.getRelationId());
					StringBuilder proName = new StringBuilder();
					Double amount = 0.0;
					for (int i = 0; i < reOrders.size(); i++) {
						TbSpOrder or = reOrders.get(i);
						amount += or.getAmount();
						if (i == 0) {
							proName.append(or.getProductName());
						} else {
							proName.append("<br>" + or.getProductName());
						}
					}
					order.setProductName(proName.toString());
					order.setAmount(amount);
				} else {
					order.setRelState("0");
				}
			}
			// Date date = new Date();
			// if (order.getEndDate().getTime() < date.getTime()) {
			// order.setStatus(TbSpOrder.STATUS_INVALID);
			// orderDao.save(order);
			// }

			// log.info(order.getProposalNo().substring(6, 8));
			// String branchCodeSub = order.getProposalNo().substring(6, 8);
			List<TbSysHotarea> areaList = null;
			// if ("33".equals(branchCodeSub)) {
			String areaCode = order.getProposalNo().substring(6, 10);
			areaList = tbSysHotareaDao.findLikeBranchCodeLike("0000");
			// } else {
			// areaList = tbSysHotareaDao.findLikeBranchCodeLike(branchCodeSub);
			// }
			if (areaList == null) {
				order.setInvoiceStatus("0");
				order.setInsurancePolicyStatus("0");
			} else {
				if (areaList.size() == 0) {
					order.setInvoiceStatus("0");
					order.setInsurancePolicyStatus("0");
				} else {
					order.setInvoiceStatus(areaList.get(0).getInvoiceStatus());
					order.setInsurancePolicyStatus(areaList.get(0).getInsurancePolicyStatus());
				}
			}
		}
		sr.setResult(page);
		return sr;
	}

	// @Override
	// public ServiceResult orderList(String insuranceDateStart, String
	// insuranceDateEnd, String payDateStart, String payDateEnd, String
	// createTimeStart,
	// String createTimeEnd, String policyNo, String proposalNo, String productName,
	// String planName, String applicantName, String idNo,
	// String insuredPersonName, String insuredPersonNo, String paymentNo, String
	// branchCode, String agreementNo, String status,
	// String refereeId, String refereeMobile, String userName, String mobile,
	// String scoreUser, String scoreUserMobile, String successfulUrl,
	// String shareUid, String callBackUrl, String orderNo, String dealUuid, int
	// pageNumber, int pageSize) {

	// ServiceResult sr = new ServiceResult();
	// Sort sort = new Sort(Direction.DESC, "id");
	// Page<TbSpOrder> page = orderDao.findAll(new Specification<TbSpOrder>() {
	// public Predicate toPredicate(Root<TbSpOrder> root, CriteriaQuery<?> query,
	// CriteriaBuilder cb) {
	// List<Predicate> predicate = new ArrayList<Predicate>();
	// if (StringUtils.isNotEmpty(insuranceDateStart) &&
	// StringUtils.isNotEmpty(insuranceDateEnd)) {
	// predicate.add(cb.greaterThanOrEqualTo(root.get("createTime").as(String.class),
	// insuranceDateStart));
	// predicate.add(cb.lessThanOrEqualTo(root.get("createTime").as(String.class),
	// insuranceDateEnd));
	// }
	// if (StringUtils.isNotEmpty(payDateStart) &&
	// StringUtils.isNotEmpty(payDateEnd)) {
	// predicate.add(cb.greaterThanOrEqualTo(root.get("payDate").as(String.class),
	// payDateStart));
	// predicate.add(cb.lessThanOrEqualTo(root.get("payDate").as(String.class),
	// payDateEnd));
	// }
	// if (StringUtils.isNotEmpty(createTimeStart) &&
	// StringUtils.isNotEmpty(createTimeEnd)) {
	// predicate.add(cb.greaterThanOrEqualTo(root.get("createTime").as(String.class),
	// createTimeStart));
	// predicate.add(cb.lessThanOrEqualTo(root.get("createTime").as(String.class),
	// createTimeEnd));
	// }
	// if (StringUtils.isNotEmpty(policyNo)) {
	// log.info(policyNo);
	// predicate.add(cb.like(root.get("policyNo").as(String.class), "%" + policyNo +
	// "%"));
	// }
	// if (StringUtils.isNotEmpty(proposalNo)) {
	// log.info(proposalNo);
	// predicate.add(cb.like(root.get("proposalNo").as(String.class), "%" +
	// proposalNo + "%"));
	// }
	// if (StringUtils.isNotEmpty(productName)) {
	// log.info(productName);
	// predicate.add(cb.like(root.get("productName").as(String.class), "%" +
	// productName + "%"));
	// }
	// if (StringUtils.isNotEmpty(planName)) {
	// log.info(planName);
	// predicate.add(cb.like(root.get("planName").as(String.class), "%" + planName +
	// "%"));
	// }
	// if (StringUtils.isNotEmpty(applicantName)) {
	// log.info(applicantName);
	// predicate.add(cb.like(root.get("applicantName").as(String.class), "%" +
	// applicantName + "%"));
	// }
	// if (StringUtils.isNotEmpty(idNo)) {
	// log.info(idNo);
	// predicate.add(cb.like(root.get("idNo").as(String.class), "%" + idNo + "%"));
	// }
	//
	// if (StringUtils.isNotEmpty(insuredPersonName)) {
	// log.info(insuredPersonName);
	// List<TbSpApplicant> applicant = applicantDao.findByName(insuredPersonName);
	// if (null != applicant && applicant.size() != 0) {
	// In<String> in = cb.in(root.get("orderNo").as(String.class));
	// for (TbSpApplicant ts : applicant) {
	// in.value(ts.getOrderNo());
	// }
	// predicate.add(in);
	// } else {
	// predicate.add(cb.equal(root.get("orderNo").as(String.class),
	// insuredPersonName));
	// }
	//
	// }
	// if (StringUtils.isNotEmpty(insuredPersonNo)) {
	// log.info(insuredPersonNo);
	// List<TbSpApplicant> applicant = applicantDao.findByCarId(insuredPersonNo);
	// if (null != applicant && applicant.size() != 0) {
	// In<String> in = cb.in(root.get("orderNo").as(String.class));
	// for (TbSpApplicant ts : applicant) {
	// in.value(ts.getOrderNo());
	// }
	// predicate.add(in);
	// } else {
	// predicate.add(cb.equal(root.get("orderNo").as(String.class),
	// insuredPersonName));
	// }
	// }
	//
	// if (StringUtils.isNotEmpty(paymentNo)) {
	// log.info(paymentNo);
	// predicate.add(cb.like(root.get("paymentNo").as(String.class), "%" + paymentNo
	// + "%"));
	// }
	// if (StringUtils.isNotEmpty(agreementNo)) {
	// log.info(agreementNo);
	// predicate.add(cb.like(root.get("agreementNo").as(String.class), "%" +
	// agreementNo + "%"));
	// }
	// if (StringUtils.isNotEmpty(status) && !"-1".equals(status)) {
	// log.info(status);
	// predicate.add(cb.equal(root.get("status").as(String.class), status));
	// }
	//
	// if (StringUtils.isNotEmpty(branchCode) && !"-1".equals(branchCode)) {
	// log.info(branchCode);
	// predicate.add(cb.equal(root.get("branchCode").as(String.class), branchCode));
	// }
	// if (StringUtils.isNotEmpty(refereeId)) {
	// log.info(refereeId);
	// String userCodeBs = "-";// -表示用户的userCode值在订单的userId字段不可能有该字符
	// TbSpUser user = userDao.findByUserCode(refereeId);
	// if (user != null && StringUtils.isNotBlank(user.getUserCodeBs())) {
	// userCodeBs = user.getUserCodeBs();
	// log.info("合并的用户[id={},code={}],有codebs值={}", user.getId(),
	// user.getUserCode(), user.getUserCodeBs());
	// }
	// predicate.add(cb.or(cb.equal(root.get("refereeId").as(String.class),
	// refereeId),
	// cb.equal(root.get("refereeId").as(String.class), userCodeBs)));
	// }
	//
	// if (StringUtils.isNotEmpty(refereeMobile)) {
	// log.info(refereeMobile);
	// List<TbSpUser> user = userDao.findByMobile(refereeMobile);
	// if (CollectionUtils.isNotEmpty(user)) {
	// predicate.add(cb.like(root.get("refereeId").as(String.class), "%" +
	// user.get(0).getUserCode() + "%"));
	// } else {
	// predicate.add(cb.like(root.get("refereeId").as(String.class), "%" +
	// refereeMobile + "%"));
	// }
	// }
	//
	// if (StringUtils.isNotEmpty(userName)) {
	// log.info(userName);
	// List<TbSpUser> user = userDao.findByUserName(userName);
	// if (CollectionUtils.isNotEmpty(user)) {
	// for (TbSpUser u : user) {
	// predicate.add(cb.or(cb.equal(root.get("userId").as(String.class),
	// u.getUserCode())));
	// }
	// } else {
	// predicate.add(cb.equal(root.get("userId").as(String.class), userName));
	// }
	// }
	//
	// if (StringUtils.isNotEmpty(mobile)) {
	// log.info(mobile);
	// List<TbSpUser> user = userDao.findByMobile(mobile);
	// if (CollectionUtils.isNotEmpty(user)) {
	// predicate.add(cb.equal(root.get("userId").as(String.class),
	// user.get(0).getUserCode()));
	// } else {
	// predicate.add(cb.equal(root.get("userId").as(String.class), mobile));
	// }
	// }
	//
	// // 积分用户
	// if (StringUtils.isNotEmpty(scoreUser)) {
	// log.info(scoreUser);
	// List<TbSpUser> user = userDao.findByUserName(scoreUser);
	// if (CollectionUtils.isNotEmpty(user)) {
	// for (TbSpUser u : user) {
	// predicate.add(cb.or(cb.equal(root.get("refereeId").as(String.class),
	// u.getUserCode())));
	// }
	// } else {
	// predicate.add(cb.equal(root.get("refereeId").as(String.class), scoreUser));
	// }
	// }
	//
	// if (StringUtils.isNotEmpty(scoreUserMobile)) {
	// log.info(scoreUserMobile);
	// List<TbSpUser> user = userDao.findByMobile(scoreUserMobile);
	// if (CollectionUtils.isNotEmpty(user)) {
	// predicate.add(cb.equal(root.get("refereeId").as(String.class),
	// user.get(0).getUserCode()));
	// } else {
	// predicate.add(cb.equal(root.get("refereeId").as(String.class),
	// scoreUserMobile));
	// }
	// }
	//
	// if (StringUtils.isNotEmpty(callBackUrl)) {
	// log.info(callBackUrl);
	// predicate.add(cb.like(root.get("callBackUrl").as(String.class), "%" +
	// callBackUrl + "%"));
	// }
	// if (StringUtils.isNotEmpty(successfulUrl)) {
	// log.info(successfulUrl);
	// predicate.add(cb.like(root.get("successfulUrl").as(String.class), "%" +
	// successfulUrl + "%"));
	// }
	// if (StringUtils.isNotEmpty(orderNo)) {
	// log.info(orderNo);
	// predicate.add(cb.like(root.get("orderNo").as(String.class), "%" + orderNo +
	// "%"));
	// }
	// if (StringUtils.isNotEmpty(shareUid)) {
	// log.info(shareUid);
	// predicate.add(cb.like(root.get("shareUid").as(String.class), "%" + shareUid +
	// "%"));
	// }
	//
	// if (StringUtils.isNotEmpty(dealUuid)) {
	// log.info(dealUuid);
	// predicate.add(cb.like(root.get("dealUuid").as(String.class), "%" + dealUuid +
	// "%"));
	// }
	// Predicate[] pre = new Predicate[predicate.size()];
	// return query.where(predicate.toArray(pre)).getRestriction();
	// }
	// }, new PageRequest(pageNumber - 1, pageSize, sort));
	// List<TbSpOrder> list = page.getContent();
	// for (TbSpOrder order : list) {
	// List<TbSpApplicant> applicant =
	// applicantDao.findAssuredByOrderNo(order.getOrderNo());
	// if (CollectionUtils.isNotEmpty(applicant)) {
	// order.setApplicant(applicant);
	// }
	// if (StringUtils.isNotEmpty(order.getRefereeId())) {
	// order.setRefereeUser(userDao.findByUserCode(order.getRefereeId()));
	// } else {
	// List<TbSpScoreLogIn> inLog =
	// scoreLogInDao.findByPolicyNo(Constants.CHANGE_TYYPE_RULE,
	// order.getPolicyNo());
	// if (CollectionUtils.isNotEmpty(inLog)) {
	// order.setRefereeUser(userDao.findByUserCode(inLog.get(0).getUserCode()));
	// }
	// }
	// if (StringUtils.isNotEmpty(order.getUserId())) {
	// order.setUser(userDao.findByUserCode(order.getUserId()));
	// }
	// if (StringUtils.isNotBlank(order.getAgentName())) {
	// order.setScoreU(false);
	// }
	// if (order.getScoreU()) {
	// String userCode = "";
	// if (StringUtils.isBlank(order.getRefereeId())) {// 是否有推荐人
	// log.info("-----没有推荐人---取当前用户---");
	// if (StringUtils.isNotBlank(order.getUserId())) {
	// userCode = order.getUserId();
	// }
	// } else {
	// userCode = order.getRefereeId();
	// }
	// if (StringUtils.isNotBlank(userCode)) {
	// List<TbSpApplicant> applicants =
	// applicantDao.findByOrderNo(order.getOrderNo());
	// TbSpUser user = userDao.findByUserCode(userCode);
	// if (CollectionUtils.isNotEmpty(applicants) && null != user) {
	// for (TbSpApplicant at : applicants) {
	// log.info("at.getCarId():{}", at.getCarId());
	// if (StringUtils.isNotBlank(at.getCarId()) &&
	// StringUtils.isNotBlank(user.getIdNumber())
	// && at.getCarId().equals(user.getIdNumber())) {
	// log.info("-----积分用户投保人、被保人是本人不显示积分用户------{}");
	// order.setScoreU(false);
	// break;
	// }
	// }
	// }
	// } else {
	// order.setScoreU(false);
	// }
	// }
	// log.info("---------{}", order.getScoreU());
	// }
	//
	//// Map<String,Object> map =
	// thisOrderList(insuranceDateStart,insuranceDateEnd,payDateStart,payDateEnd,createTimeStart,createTimeEnd,policyNo,
	//// proposalNo,productName,planName,applicantName,idNo,insuredPersonName,insuredPersonNo,paymentNo,branchCode,agreementNo,status,refereeId,refereeMobile,userName,mobile,scoreUser,scoreUserMobile,successfulUrl,shareUid,callBackUrl,orderNo,dealUuid);
	//// map.put("list", page);
	// sr.setResult(page);
	// return sr;
	// }

	@Override
	public ServiceResult orderList(String comCode, String insuranceDateStart, String insuranceDateEnd, String payDateStart, String payDateEnd,
			String createTimeStart, String createTimeEnd, String policyNo, String proposalNo, String productName, String planName,
			String applicantName, String idNo, String insuredPersonName, String insuredPersonNo, String paymentNo, String branchCode,
			String agreementNo, String status, String refereeId, String refereeMobile, String userName, String mobile, String scoreUser,
			String scoreUserMobile, String successfulUrl, String shareUid, String callBackUrl, String orderNo, String dealUuid, int pageNumber,
			int pageSize) {
		ServiceResult sr = new ServiceResult();
		Sort sort = new Sort(Direction.DESC, "id");
		List<TbSpOrder> list = orderDao.findAll(new Specification<TbSpOrder>() {
			public Predicate toPredicate(Root<TbSpOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> predicate = new ArrayList<Predicate>();
				if (StringUtils.isNotEmpty(comCode)) {
					if (!"00000000".equals(comCode)) {
						Set<String> newList = new HashSet();
						List<TbSpUser> users = userDao.findByComCode(comCode);
						if (CollectionUtils.isNotEmpty(users)) {
							for (TbSpUser u : users) {
								newList.add(u.getUserCode());
							}
						}
						List<SysBranch> branchs = sysBranchDao.findNextBranch(comCode);
						if (CollectionUtils.isNotEmpty(branchs)) {
							for (SysBranch branch : branchs) {
								newList.add(branch.getBranchNo());
							}
						}
						log.info("----newList----{}", newList.toString());
						Set<String> allcom = new HashSet();
						allcom = levelService.findAllNextBranchNo(newList, allcom);
						log.info("----allcom----{}", allcom.toString());
						allcom.add(comCode);
						allcom.addAll(newList);
						log.info("----allcom----{}", allcom.toString());
						if (CollectionUtils.isNotEmpty(allcom)) {
							In<String> in = cb.in(root.get("comCode").as(String.class));
							for (String ts : allcom) {
								in.value(ts);
							}
							predicate.add(in);
						} else {
							predicate.add(cb.equal(root.get("comCode").as(String.class), "LL"));
						}
					}
				} else {
					predicate.add(cb.equal(root.get("comCode").as(String.class), "LL"));
				}
				if (StringUtils.isNotEmpty(insuranceDateStart) && StringUtils.isNotEmpty(insuranceDateEnd)) {
					predicate.add(cb.greaterThanOrEqualTo(root.get("createTime").as(String.class), insuranceDateStart));
					predicate.add(cb.lessThanOrEqualTo(root.get("createTime").as(String.class), insuranceDateEnd));
				}
				if (StringUtils.isNotEmpty(payDateStart) && StringUtils.isNotEmpty(payDateEnd)) {
					predicate.add(cb.greaterThanOrEqualTo(root.get("payDate").as(String.class), payDateStart));
					predicate.add(cb.lessThanOrEqualTo(root.get("payDate").as(String.class), payDateEnd));
				}
				if (StringUtils.isNotEmpty(createTimeStart) && StringUtils.isNotEmpty(createTimeEnd)) {
					predicate.add(cb.greaterThanOrEqualTo(root.get("createTime").as(String.class), createTimeStart));
					predicate.add(cb.lessThanOrEqualTo(root.get("createTime").as(String.class), createTimeEnd));
				}
				if (StringUtils.isNotEmpty(policyNo)) {
					log.info(policyNo);
					predicate.add(cb.like(root.get("policyNo").as(String.class), "%" + policyNo + "%"));
				}
				if (StringUtils.isNotEmpty(proposalNo)) {
					log.info(proposalNo);
					predicate.add(cb.like(root.get("proposalNo").as(String.class), "%" + proposalNo + "%"));
				}
				if (StringUtils.isNotEmpty(productName)) {
					log.info(productName);
					predicate.add(cb.like(root.get("productName").as(String.class), "%" + productName + "%"));
				}
				if (StringUtils.isNotEmpty(planName)) {
					log.info(planName);
					predicate.add(cb.like(root.get("planName").as(String.class), "%" + planName + "%"));
				}
				if (StringUtils.isNotEmpty(applicantName)) {
					log.info(applicantName);
					predicate.add(cb.like(root.get("applicantName").as(String.class), "%" + applicantName + "%"));
				}
				if (StringUtils.isNotEmpty(idNo)) {
					log.info(idNo);
					predicate.add(cb.like(root.get("idNo").as(String.class), "%" + idNo + "%"));
				}

				if (StringUtils.isNotEmpty(insuredPersonName)) {
					log.info(insuredPersonName);
					List<TbSpApplicant> applicant = applicantDao.findByName(insuredPersonName);
					if (null != applicant && applicant.size() != 0) {
						In<String> in = cb.in(root.get("orderNo").as(String.class));
						for (TbSpApplicant ts : applicant) {
							in.value(ts.getOrderNo());
						}
						predicate.add(in);
					} else {
						predicate.add(cb.equal(root.get("orderNo").as(String.class), insuredPersonName));
					}

				}
				if (StringUtils.isNotEmpty(insuredPersonNo)) {
					log.info(insuredPersonNo);
					List<TbSpApplicant> applicant = applicantDao.findByCarId(insuredPersonNo);
					if (null != applicant && applicant.size() != 0) {
						In<String> in = cb.in(root.get("orderNo").as(String.class));
						for (TbSpApplicant ts : applicant) {
							in.value(ts.getOrderNo());
						}
						predicate.add(in);
					} else {
						predicate.add(cb.equal(root.get("orderNo").as(String.class), insuredPersonName));
					}
				}

				if (StringUtils.isNotEmpty(paymentNo)) {
					log.info(paymentNo);
					predicate.add(cb.like(root.get("paymentNo").as(String.class), "%" + paymentNo + "%"));
				}
				if (StringUtils.isNotEmpty(agreementNo)) {
					log.info(agreementNo);
					predicate.add(cb.like(root.get("agreementNo").as(String.class), "%" + agreementNo + "%"));
				}
				if (StringUtils.isNotEmpty(status)) {
					log.info(status);
					predicate.add(cb.equal(root.get("status").as(String.class), status));
				}

				if (StringUtils.isNotEmpty(branchCode) && !"-1".equals(branchCode)) {
					log.info(branchCode);
					predicate.add(cb.equal(root.get("branchCode").as(String.class), branchCode));
				}
				if (StringUtils.isNotEmpty(refereeId)) {
					log.info(refereeId);
					String userCodeBs = "-";// -表示用户的userCode值在订单的userId字段不可能有该字符
					TbSpUser user = userDao.findByUserCode(refereeId);
					if (user != null && StringUtils.isNotBlank(user.getUserCodeBs())) {
						userCodeBs = user.getUserCodeBs();
						log.info("合并的用户[id={},code={}],有codebs值={}", user.getId(), user.getUserCode(), user.getUserCodeBs());
					}
					predicate.add(cb.or(cb.equal(root.get("refereeId").as(String.class), refereeId),
							cb.equal(root.get("refereeId").as(String.class), userCodeBs)));
				}

				if (StringUtils.isNotEmpty(refereeMobile)) {
					log.info(refereeMobile);
					List<TbSpUser> user = userDao.findByMobile(refereeMobile);
					if (CollectionUtils.isNotEmpty(user)) {
						predicate.add(cb.like(root.get("refereeId").as(String.class), "%" + user.get(0).getUserCode() + "%"));
					} else {
						predicate.add(cb.like(root.get("refereeId").as(String.class), "%" + refereeMobile + "%"));
					}
				}

				if (StringUtils.isNotEmpty(userName)) {
					log.info(userName);
					List<TbSpUser> user = userDao.findByUserName(userName);
					if (CollectionUtils.isNotEmpty(user)) {
						for (TbSpUser u : user) {
							predicate.add(cb.or(cb.equal(root.get("userId").as(String.class), u.getUserCode())));
						}
					} else {
						predicate.add(cb.equal(root.get("userId").as(String.class), userName));
					}
				}

				if (StringUtils.isNotEmpty(mobile)) {
					log.info(mobile);
					List<TbSpUser> user = userDao.findByMobile(mobile);
					if (CollectionUtils.isNotEmpty(user)) {
						predicate.add(cb.equal(root.get("userId").as(String.class), user.get(0).getUserCode()));
					} else {
						predicate.add(cb.equal(root.get("userId").as(String.class), mobile));
					}
				}

				// 积分用户
				if (StringUtils.isNotEmpty(scoreUser)) {
					log.info(scoreUser);
					List<TbSpUser> user = userDao.findByUserName(scoreUser);
					if (CollectionUtils.isNotEmpty(user)) {
						for (TbSpUser u : user) {
							predicate.add(cb.or(cb.equal(root.get("refereeId").as(String.class), u.getUserCode())));
						}
					} else {
						predicate.add(cb.equal(root.get("refereeId").as(String.class), scoreUser));
					}
				}

				if (StringUtils.isNotEmpty(scoreUserMobile)) {
					log.info(scoreUserMobile);
					List<TbSpUser> user = userDao.findByMobile(scoreUserMobile);
					if (CollectionUtils.isNotEmpty(user)) {
						predicate.add(cb.equal(root.get("refereeId").as(String.class), user.get(0).getUserCode()));
					} else {
						predicate.add(cb.equal(root.get("refereeId").as(String.class), scoreUserMobile));
					}
				}
				if (StringUtils.isNotEmpty(callBackUrl)) {
					log.info(callBackUrl);
					predicate.add(cb.like(root.get("callBackUrl").as(String.class), "%" + callBackUrl + "%"));
				}
				if (StringUtils.isNotEmpty(successfulUrl)) {
					log.info(successfulUrl);
					predicate.add(cb.like(root.get("successfulUrl").as(String.class), "%" + successfulUrl + "%"));
				}
				if (StringUtils.isNotEmpty(orderNo)) {
					log.info(orderNo);
					predicate.add(cb.like(root.get("orderNo").as(String.class), "%" + orderNo + "%"));
				}
				if (StringUtils.isNotEmpty(shareUid)) {
					log.info(shareUid);
					// if (!"henghua".equals(shareUid)) {
					predicate.add(cb.like(root.get("shareUid").as(String.class), "%" + shareUid + "%"));
					// }
				}

				if (StringUtils.isNotEmpty(dealUuid)) {
					log.info(dealUuid);
					predicate.add(cb.like(root.get("dealUuid").as(String.class), "%" + dealUuid + "%"));
				}
				Predicate[] pre = new Predicate[predicate.size()];
				return query.where(predicate.toArray(pre)).getRestriction();
			}
		}, sort);

		Double totalAmount = 0.00;
		for (TbSpOrder order : list) {
			totalAmount = new BigDecimal(totalAmount).add(new BigDecimal(null != order.getAmount() ? order.getAmount() : 0))
					.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
		}

		int size = list.size();
		// Double totalAmount = new BigDecimal(total).divide(new
		// BigDecimal(size)).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
		log.info("------totalAmount-----:{}", totalAmount);
		Double avgAmount = null;
		if (size == 0) {
			avgAmount = 0.00;
		} else {
			avgAmount = new BigDecimal(totalAmount).divide(new BigDecimal(size), 2, BigDecimal.ROUND_HALF_UP).doubleValue();
		}
		log.info("------avgAmount-----:{}", avgAmount);
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("totalAmount", totalAmount);
		map.put("avgAmount", avgAmount);
		int startPageNumber = (pageNumber - 1) * pageSize;
		int endPageNumber = pageNumber * pageSize;
		List<TbSpOrder> backList = list.subList(startPageNumber <= size ? startPageNumber : size, endPageNumber <= size ? endPageNumber : size);

		for (TbSpOrder order : backList) {
			List<TbSpApplicant> applicant = applicantDao.findAssuredByOrderNo(order.getOrderNo());
			if (CollectionUtils.isNotEmpty(applicant)) {
				order.setApplicant(applicant);
			}
			TbSpUser scoreU = scoreService.scoreUser(order);
			if (null != scoreU) {
				if (scoreU.getRegisterType().equals(Constants.USER_REGISTER_TYPE_0)) {
					List<TbSpScoreLogIn> inLogs = scoreLogInDao.findByUCodeAndPolicyNo(scoreU.getUserCode(), order.getPolicyNo());
					if (CollectionUtils.isNotEmpty(inLogs)) {
						order.setRefereeUser(scoreU);
					}
				} else {
					// 上级
					TbSpUser upUser = userDao.findByUserCode(scoreU.getComCode());
					if (null != upUser && upUser.getCanScore().equals(Constants.TRUE)) {
						order.setRefereeUser(scoreU);
					}
				}
			}
			if (StringUtils.isNotEmpty(order.getUserId())) {
				order.setUser(userDao.findByUserCode(order.getUserId()));
			}
			// if (StringUtils.isNotBlank(order.getAgentName())) {
			// order.setScoreU(false);
			// }
			// if (order.getScoreU()) {
			// String userCode = "";
			// if (StringUtils.isBlank(order.getRefereeId())) {// 是否有推荐人
			// log.info("-----没有推荐人---取当前用户---");
			// if (StringUtils.isNotBlank(order.getUserId())) {
			// userCode = order.getUserId();
			// }
			// } else {
			// userCode = order.getRefereeId();
			// }
			// if (StringUtils.isNotBlank(userCode)) {
			// List<TbSpApplicant> applicants =
			// applicantDao.findByOrderNo(order.getOrderNo());
			// TbSpUser user = userDao.findByUserCode(userCode);
			// if (CollectionUtils.isNotEmpty(applicants) && null != user) {
			// for (TbSpApplicant at : applicants) {
			// log.info("at.getCarId():{}", at.getCarId());
			// if (StringUtils.isNotBlank(at.getCarId()) &&
			// StringUtils.isNotBlank(user.getIdNumber())
			// && at.getCarId().equals(user.getIdNumber())) {
			// log.info("-----积分用户投保人、被保人是本人不显示积分用户------{}");
			// order.setScoreU(false);
			// break;
			// }
			// }
			// }
			// } else {
			// order.setScoreU(false);
			// }
			// }
			// log.info("---------{}", order.getScoreU());
			if (null != order.getRefereeUser()) {
				String thisUserCode = order.getRefereeUser().getUserCode();
				List<String> changeType1 = Lists.newArrayList();
				changeType1.add(Constants.CHANGE_TYYPE_RULE);
				changeType1.add(Constants.CHANNEL_ISSUE);
				TbSpScoreLogIn thisIn = scoreLogInDao.findByUCodeAndPolicyNoAndType(thisUserCode, policyNo, changeType1);
				if (null != thisIn) {
					order.getRefereeUser().setBaseRate(thisIn.getReChangeScore());
				}
				String thisComCode = order.getRefereeUser().getComCode();
				if (StringUtils.isNotBlank(thisComCode) && (thisComCode.startsWith("WX") || thisComCode.startsWith("BS"))) {
					List<String> upNumbers = Lists.newArrayList();
					List<String> topUsers = Lists.newArrayList();
					topUsers = findAllTopUser(thisUserCode, upNumbers);
					if (CollectionUtils.isNotEmpty(topUsers)) {
						List<Object> upUserScore = Lists.newArrayList();
						List<String> changeType = Lists.newArrayList();
						changeType.add(Constants.CHANNEL_ISSUE);
						changeType.add(Constants.DIRECT_FRIEND_ISSUE);
						changeType.add(Constants.INDIRECT_FRIEND_ISSUE);

						for (int i = topUsers.size() - 1; i >= 0; i--) {
							Object inLogs = scoreLogInDao.findByUpUserScore(topUsers.get(i), order.getPolicyNo(), changeType);
							upUserScore.add(inLogs);
						}
						order.setUpUserSocre(upUserScore);
					}
				}
			}
		}
		map.put("list", backList);
		map.put("totalSize", size);
		sr.setResult(map);
		return sr;
	}

	public List<String> findAllTopUser(String userCode, List<String> upNumbers) {
		TbSpUser user = userDao.findByUserCode(userCode);
		if (null != user) {
			if (StringUtils.isNotBlank(user.getComCode()) && (user.getComCode().startsWith("WX") || user.getComCode().startsWith("BS"))) {
				upNumbers.add(user.getComCode());
				findAllTopUser(user.getComCode(), upNumbers);
			}
		}
		return upNumbers;
	}

	@Override
	public ServiceResult saveOrder(TbSpOrder spOrder) {
		ServiceResult sr = new ServiceResult();
		try {
			orderDao.save(spOrder);
			sr.setSuccess();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			sr.setFail();
		}
		return sr;
	}

	/**
	 * 组装查询接口webservice请求报文
	 * 
	 * @param policyNoAuto
	 *            保险单号
	 * @param identifyNumberAuto
	 *            证件号码
	 * @return
	 */
	public static String eipRequestXml(String policyNoAuto, String identifyNumberAuto) {
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<QueryData>");
		sb.append("<Data>");
		sb.append("<CertiNoData>");
		sb.append("<certiNo>" + policyNoAuto + "</certiNo>");
		sb.append("<policyNo>" + policyNoAuto + "</policyNo>");
		sb.append("<isCheck>1</isCheck>");
		sb.append("<identityNo>" + identifyNumberAuto + "</identityNo>");
		sb.append("</CertiNoData>");
		sb.append("</Data>");
		sb.append("</QueryData>");
		return sb.toString();
	}

	/**
	 * 递归遍历xml方法，把节点类容以Map方式输出 找到该节点后抛出异常，退出递归
	 * 
	 * @param element
	 * @param nodeName
	 *            对比节点
	 */
	public static void getElementMap(Element element, Map<String, Object> map) {
		String rootName = element.getName();

		List elements = element.elements();
		if (elements.size() == 0) {
			// 没有子元素
			String xpath = element.getPath();
			String value = element.getTextTrim();
			map.put(rootName, value);
		} else {
			// 有子元素
			for (Iterator it = elements.iterator(); it.hasNext();) {
				Element elem = (Element) it.next();
				// 递归遍历
				getElementMap(elem, map);
			}
		}
	}

	@Override
	public ServiceResult findOrder(HttpServletRequest request, String valCode, String type, String findData, String policyNo) {
		ServiceResult sr = new ServiceResult(Constants.SYS_ERROR_MSG, ServiceResult.STATE_EXCEPTION);
		try {
			// 校验-图形码
			if (!captchaService.validationCaptcha(request, sr, redisUtils, valCode)) {
				return sr;// 信息转存
			}
			List<TbSpOrder> orList = null;
			if ("name".equals(type)) {
				orList = orderDao.findOrderName(findData, policyNo);
			} else {
				orList = orderDao.findOrderIdNo(findData, policyNo);
			}
			if (orList.size() > 0) {
				sr.setSuccess();
				sr.setResult(orList.get(0));
			} else {
				sr.setFail();
				sr.setResult("未查询到相关订单信息！");
			}
		} catch (Exception e) {
			log.error(e.toString());
			sr.setFail();
			sr.setResult("查询失败");
		}
		return sr;
	}

	@Override
	public void SetInvalidOrder(String userId) {
		List<TbSpOrder> orList = orderDao.findAllByUserId(userId);
		Date nowDate = new Date();
		for (int i = 0; i < orList.size(); i++) {
			TbSpOrder order = orList.get(i);
			if (order.getStatus().equals(TbSpOrder.STATUS_NO_PAY)) {
				Date startDate = order.getStartDate();
				if (nowDate.getTime() > startDate.getTime()) {
					order.setStatus(TbSpOrder.STATUS_INVALID);
					orderDao.save(order);
				}
			}
		}
	}

	@Override
	public ServiceResult downBJPolicy(String policyNo, String proposalDate, String emailStr, String applicantName, HttpServletResponse response) {
		ServiceResult rs = new ServiceResult();

		if (StringUtil.isEmpty(policyNo)) {
			rs.setFail();
			rs.setResult("保单号不能为空！");
			return rs;
		}
		Map map = (Map) redisUtils.get(Constants.SYS_SERVICE_INFO);
		SysServiceInfo sysServiceInfo = (SysServiceInfo) map.get(CoreServiceEnum.Down_BJPolicy_URL.getUrlKey());
		/*
		 * HttpHeaders headers = new HttpHeaders(); headers.add("account-code",
		 * sysServiceInfo.getUserName()); headers.add("request-time", String.valueOf(new
		 * Date().getTime())); headers.add("soa-token",
		 * pwdEncoder.encodePassword(sysServiceInfo.getPassword(), String.valueOf(new
		 * Date().getTime())));
		 */
		TDownFileRequestDto reqeust = new TDownFileRequestDto();
		reqeust.setPartnerAccountCode(sysServiceInfo.getUserName());
		reqeust.setBusinessNo(policyNo);
		reqeust.setPartnerAccountCode(sysServiceInfo.getUserName());
		reqeust.setFlowId(sysServiceInfo.getUserName() + DateUtil.dateFromat(new Date(), DateUtil.DATE_TIME_PATTERN3) + new Date().getTime());
		HttpEntity<TDownFileRequestDto> requestEntity = new HttpEntity<TDownFileRequestDto>(reqeust,
				RequestUtils.genEncryptHttpHeaders(sysServiceInfo.getUserName(), sysServiceInfo.getPassword()));
		// sysServiceInfo.setUrl("http://10.132.30.113:8850/downEPolicy");
		TDownFileResponseDto responseDto = restTemplate.postForObject(sysServiceInfo.getUrl(), requestEntity, TDownFileResponseDto.class);
		if (StringUtil.isNotEmpty(emailStr) && null != responseDto && null != responseDto.getPdfFileByte()) {// 发送邮件
			Map<String, byte[]> attachmentsByts = new HashMap<String, byte[]>();
			attachmentsByts.put(responseDto.getFileName(), responseDto.getPdfFileByte());
			try {
				boolean flag = emailService.sendEmail("利宝保险电子保单", CommonUtil.fetchSendMessage(applicantName), ImmutableList.of(emailStr), null, null,
						attachmentsByts, true);
			} catch (Exception e) {
				rs.setFail();
				rs.setResult("邮件发送失败！");
				return rs;
			}
		} else {
			try {
				ResponseUtils.download(responseDto.getFileName(), responseDto.getPdfFileByte(), response);
			} catch (IOException e) {
				rs.setFail();
				rs.setResult("文件输出失败！");
				return rs;
			}

		}
		return rs;
	}

	@Override
	public ServiceResult performance(String userCode, String queryType, String queryParam, String userCodeBs) {
		ServiceResult rs = new ServiceResult();
		rs.setFail();
		try {
			if (StringUtil.isEmpty(userCode) || StringUtil.isEmpty(queryType)) {
				rs.setResult("请求参数不能为空！");
				return rs;
			}
			List<Map<String, Object>> list = null;
			String sql = "";

			if ("D".equals(queryType)) {
				// sql="select DATE_FORMAT(o.CREATE_TIME,'%Y-%m-%d') as days,sum(o.AMOUNT) as
				// amount From tb_sp_order o where ( USER_ID=? or Referee_ID =? ) and STATUS='1'
				// and o.Policy_No<>'' and DATE_FORMAT(o.CREATE_TIME,'%Y')=? GROUP BY
				// DATE_FORMAT(o.CREATE_TIME,'%d') ORDER BY DATE_FORMAT(o.CREATE_TIME,'%d') ";
				// list= readJdbcTemplate.queryForList(sql, new
				// Object[]{userCode,userCode,queryParam});

				// sql = "select DATE_FORMAT(o.CREATE_TIME,'%d') as days,sum(o.AMOUNT) as amount
				// From tb_sp_order o where ( USER_ID=? or Referee_ID =? or USER_ID=? or
				// Referee_ID=? ) and STATUS='1' and o.Policy_No<>'' and
				// DATE_FORMAT(o.CREATE_TIME,'%Y%m')=? GROUP BY DATE_FORMAT(o.CREATE_TIME,'%d')
				// ORDER BY DATE_FORMAT(o.CREATE_TIME,'%d') ";
				// list = readJdbcTemplate.queryForList(sql, new Object[] { userCode,
				// userCode,userCodeBs,userCodeBs, queryParam });

				sql = "select DATE_FORMAT(o.CREATE_TIME,'%d') as days,sum(o.AMOUNT)  as amount From tb_sp_order  o where ( Referee_ID =? OR Referee_ID =? OR (Referee_ID = '' AND USER_ID =?) OR (Referee_ID = '' AND USER_ID =?)) and STATUS IN('1','3') and o.Policy_No<>'' and DATE_FORMAT(o.CREATE_TIME,'%Y%m')=?  GROUP BY DATE_FORMAT(o.CREATE_TIME,'%d')  ORDER BY DATE_FORMAT(o.CREATE_TIME,'%d') ";
				list = readJdbcTemplate.queryForList(sql, new Object[] { userCode, userCodeBs, userCode, userCodeBs, queryParam });

				// list=orderDao.performanceByDay(userCode,queryParam);
			} else if ("M".equals(queryType)) {
				String[] stList = queryParam.split(",");
				Map<String, List<Map<String, Object>>> dataMap = new HashMap<String, List<Map<String, Object>>>();
				for (int i = 0; i < stList.length; i++) {
					List<Map<String, Object>> datalist = null;
					// sql = "select DATE_FORMAT(o.CREATE_TIME,'%m') as days,sum(o.AMOUNT) as amount
					// From tb_sp_order o where ( USER_ID=? or Referee_ID =? or USER_ID=? or
					// Referee_ID =? ) and STATUS='1' and o.Policy_No<>'' and
					// DATE_FORMAT(o.CREATE_TIME,'%Y')=? GROUP BY DATE_FORMAT(o.CREATE_TIME,'%m')
					// ORDER BY DATE_FORMAT(o.CREATE_TIME,'%m') ";
					// datalist = readJdbcTemplate.queryForList(sql, new Object[] { userCode,
					// userCode,userCodeBs,userCodeBs, stList[i] });

					sql = "select DATE_FORMAT(o.CREATE_TIME,'%m') as days,sum(o.AMOUNT)  as amount  From tb_sp_order  o where ( Referee_ID =? OR Referee_ID =? OR (Referee_ID = '' AND USER_ID =?) OR (Referee_ID = '' AND USER_ID =?)) and STATUS IN('1','3') and o.Policy_No<>'' and DATE_FORMAT(o.CREATE_TIME,'%Y')=?  GROUP BY DATE_FORMAT(o.CREATE_TIME,'%m') ORDER BY  DATE_FORMAT(o.CREATE_TIME,'%m') ";
					datalist = readJdbcTemplate.queryForList(sql, new Object[] { userCode, userCodeBs, userCode, userCodeBs, stList[i] });
					dataMap.put(stList[i], datalist);
				}
				rs.setResult(dataMap);
				rs.setSuccess();
				return rs;
				// sql="select DATE_FORMAT(o.CREATE_TIME,'%m') as days,sum(o.AMOUNT) as amount
				// From tb_sp_order o where ( USER_ID=? or Referee_ID =? ) and STATUS='1' and
				// o.Policy_No<>'' and DATE_FORMAT(o.CREATE_TIME,'%Y')=? GROUP BY
				// DATE_FORMAT(o.CREATE_TIME,'%m') ORDER BY DATE_FORMAT(o.CREATE_TIME,'%m') ";
				// list= readJdbcTemplate.queryForList(sql, new
				// Object[]{userCode,userCode,queryParam});
				// list=orderDao.performanceByMoth(userCode,queryParam);
			} else if ("Y".equals(queryType)) {
				// sql = "select DATE_FORMAT(o.CREATE_TIME,'%Y') as days,sum(o.AMOUNT) as amount
				// From tb_sp_order o where ( USER_ID=? or Referee_ID =? or USER_ID=? or
				// Referee_ID =? ) and STATUS='1' and o.Policy_No<>'' GROUP BY
				// DATE_FORMAT(o.CREATE_TIME,'%Y') ORDER BY DATE_FORMAT(o.CREATE_TIME,'%Y') ";
				// list = readJdbcTemplate.queryForList(sql, new Object[] { userCode,
				// userCode,userCodeBs,userCodeBs });

				sql = "select DATE_FORMAT(o.CREATE_TIME,'%Y') as days,sum(o.AMOUNT)  as amount  From tb_sp_order  o where ( Referee_ID =? OR Referee_ID =? OR (Referee_ID = '' AND USER_ID =?) OR (Referee_ID = '' AND USER_ID =?)) and STATUS IN('1','3') and o.Policy_No<>''   GROUP BY DATE_FORMAT(o.CREATE_TIME,'%Y') ORDER BY DATE_FORMAT(o.CREATE_TIME,'%Y') ";
				list = readJdbcTemplate.queryForList(sql, new Object[] { userCode, userCodeBs, userCode, userCodeBs });
				// list=orderDao.performanceByYear(userCode);

			}
			rs.setResult(list);
			rs.setSuccess();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			e.printStackTrace();
		}

		// INSERT INTO tb_sp_keyword(content,type) VALUES ('利宝',1)
		return rs;
	}

	@Override
	public ServiceResult performanceList(String userCode, String riskCode, String queryParam, String userCodeBs) {
		ServiceResult rs = new ServiceResult();
		rs.setFail();
		String sql = "";
		List<Map<String, Object>> listMap = null;
		try {
			if (StringUtil.isEmpty(userCode) || StringUtil.isEmpty(queryParam)) {
				rs.setResult("请求参数不能为空！");
				return rs;
			}
			// List<TbSpOrder> list=null;
			if ("all".equals(riskCode)) {
				sql = "select o.PRODUCT_NAME as productName,o.risk_code as riskCode,o.risk_name as riskName,o.plan_name as planName,o.amount as amount,DATE_FORMAT(o.create_Time, '%Y-%c-%d %H:%i:%s') as createTime,"
						+ "a.name as name from tb_sp_order o,tb_sp_applicant a  where Policy_No<> ''"
						+ "  and (Referee_ID =? OR Referee_ID =? OR (Referee_ID = '' AND USER_ID =?) OR (Referee_ID = '' AND USER_ID =?  ))"
						+ " and DATE_FORMAT(o.CREATE_TIME,'%Y%m') = ? " + "  and a.ORDER_NO=o.ORDER_NO AND a.TYPE='1'  and  o.STATUS IN ('1','3') "
						+ "  ORDER BY o.CREATE_TIME desc ";
				listMap = readJdbcTemplate.queryForList(sql, new Object[] { userCode, userCodeBs, userCode, userCodeBs, queryParam });
				// list=orderDao.performanceList(userCode,queryParam);//按月来查询
			} else {
				sql = "select o.PRODUCT_NAME as productName,o.risk_code as riskCode,o.plan_name as planName,o.risk_name as riskName,o.amount as amount,DATE_FORMAT(o.create_Time, '%Y-%c-%d %H:%i:%s') as createTime,"
						+ "a.name as name from tb_sp_order o,tb_sp_applicant a  where Policy_No<> ''"
						+ "and o.risk_code=?   and (Referee_ID =? OR Referee_ID =? OR (Referee_ID = '' AND USER_ID =?) OR (Referee_ID = '' AND USER_ID =? ))"
						+ " and DATE_FORMAT(o.CREATE_TIME,'%Y%m') = ? " + "  and a.ORDER_NO=o.ORDER_NO AND a.TYPE='1'  and  o.STATUS IN ('1','3')"
						+ "  ORDER BY o.CREATE_TIME desc ";
				listMap = readJdbcTemplate.queryForList(sql, new Object[] { riskCode, userCode, userCodeBs, userCode, userCodeBs, queryParam });

				// list=orderDao.performanceList(userCode,queryParam,riskCode);//按月来查询
			}

			rs.setResult(listMap);
			rs.setSuccess();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			e.printStackTrace();
		}
		return rs;
	}

	@Override
	public ServiceResult performanceDetail(String userCode, String queryType, String queryParam, String userCodeBs) {
		ServiceResult rs = new ServiceResult();
		rs.setFail();
		List<Map<String, Object>> listMap = null;
		try {
			if (StringUtil.isEmpty(userCode) || StringUtil.isEmpty(queryParam)) {
				rs.setResult("请求参数不能为空！");
				return rs;
			}
			List<TbSpOrder> list = null;

			if ("y".equals(queryType)) {
				list = orderDao.performanceYear(userCode, userCodeBs, queryParam);
				rs.setResult(list);
			} else if ("m".equals(queryType)) {
				list = orderDao.performanceList(userCode, userCodeBs, queryParam);// 按月来查询
				rs.setResult(list);
			} else if ("d".equals(queryType)) {
				String sql = "select o.PRODUCT_NAME as productName,o.risk_code as riskCode,o.risk_name as riskName,o.plan_name as planName,o.amount as amount,DATE_FORMAT(o.create_Time, '%Y-%c-%d %H:%i:%s') as createTime,"
						+ "a.name as name from tb_sp_order o,tb_sp_applicant a  where Policy_No<> ''"
						+ "  and (Referee_ID =? OR Referee_ID =? OR (Referee_ID = '' AND USER_ID =?) OR (Referee_ID = '' AND USER_ID =?)  )\r\n"
						+ " and DATE_FORMAT(o.CREATE_TIME,'%Y%m%d') = ? "
						+ "  and a.ORDER_NO=o.ORDER_NO AND a.TYPE='1' and o.STATUS IN ('1','3')  ORDER BY o.CREATE_TIME desc ";
				listMap = readJdbcTemplate.queryForList(sql, new Object[] { userCode, userCodeBs, userCode, userCodeBs, queryParam });
				// list=orderDao.performanceDay(userCode,queryParam);
				rs.setResult(listMap);
			}

			// rs.setResult(list);
			rs.setSuccess();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			e.printStackTrace();
		}
		return rs;
	}

	@Override
	public ServiceResult performanceCountMonth(String userCode, String userCodeBs) {
		ServiceResult sr = new ServiceResult();
		List<Map<String, Object>> list = null;
		// String sql = "SELECT DATE_FORMAT(o.CREATE_TIME, '%Y%m') AS days,
		// sum(o.AMOUNT) AS amount FROM tb_sp_order o WHERE (USER_ID =? OR Referee_ID =?
		// or USER_ID =? OR Referee_ID =?) AND STATUS = '1' AND o.Policy_No <> '' GROUP
		// BY DATE_FORMAT(o.CREATE_TIME, '%Y%m')";
		// list = readJdbcTemplate.queryForList(sql, new Object[] { userCode,
		// userCode,userCodeBs,userCodeBs });
		String sql = "SELECT DATE_FORMAT(o.CREATE_TIME, '%Y%m') AS days, sum(o.AMOUNT) AS amount FROM tb_sp_order o WHERE ( Referee_ID =? OR Referee_ID =? OR (Referee_ID = '' and USER_ID=?) OR (Referee_ID = '' and USER_ID=?)) AND  STATUS IN('1','3')  AND o.Policy_No <> '' GROUP BY DATE_FORMAT(o.CREATE_TIME, '%Y%m')";
		list = readJdbcTemplate.queryForList(sql, new Object[] { userCode, userCodeBs, userCode, userCodeBs });
		sr.setResult(list);
		return sr;
	}

	@Override
	public ServiceResult findBySql(String sql) {
		ServiceResult sr = new ServiceResult();
		try {
			if (StringUtils.isNotBlank(sql) && -1 != sql.toLowerCase().indexOf("select")) {
				sql = sql + " limit 0,30";
				List<Map<String, Object>> proList = readJdbcTemplate.queryForList(sql);
				log.info("------sql--------{}", proList.toString());
				sr.setResult(proList);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sr;
	}

	@Override
	public ServiceResult findPolicyInfo(String orderNo, String name, String mobile, int pageNumber, int pageSize) {
		ServiceResult sr = new ServiceResult();
		Sort sort = new Sort(Direction.DESC, "id");
		Page<TbSpApplicant> page = applicantDao.findAll(new Specification<TbSpApplicant>() {
			public Predicate toPredicate(Root<TbSpApplicant> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> predicate = new ArrayList<Predicate>();
				if (StringUtils.isNotEmpty(name)) {
					log.info(name);
					predicate.add(cb.like(root.get("name").as(String.class), "%" + name + "%"));
				}
				if (StringUtils.isNotEmpty(mobile)) {
					log.info(mobile);
					predicate.add(cb.like(root.get("mobile").as(String.class), "%" + mobile + "%"));
				}

				if (StringUtils.isNotEmpty(orderNo)) {
					log.info(orderNo);
					predicate.add(cb.like(root.get("orderNo").as(String.class), "%" + orderNo + "%"));
				}
				Predicate[] pre = new Predicate[predicate.size()];
				return query.where(predicate.toArray(pre)).getRestriction();
			}
		}, PageRequest.of(pageNumber - 1, pageSize, sort));
		sr.setResult(page);
		return sr;
	}

	@Override
	public ServiceResult upOrder(TbSpOrder tbSpOrder) {
		ServiceResult sr = new ServiceResult();
		log.info("---order id---{}", tbSpOrder.getId());
		if (null == tbSpOrder.getId()) {
			sr.setFail();
			sr.setResult("无效请求！");
			return sr;
		}
		sr.setResult(orderDao.save(tbSpOrder));
		return sr;
	}

	@Override
	public ServiceResult addPolicy(TbSpApplicant applicant) {
		ServiceResult sr = new ServiceResult();
		sr.setResult(applicantDao.save(applicant));
		return sr;
	}

	@Override
	public ServiceResult countProductList() {
		ServiceResult sr = new ServiceResult();
		List<Map<String, Object>> list = null;
		Map<String, Object> map = new HashMap<String, Object>();
		String sql = " select PRODUCT_NAME productName,count(*) count from "
				+ " tb_sp_order where STATUS = '1' "
				+ " GROUP BY PRODUCT_NAME ORDER BY count(*) desc ";
		list = readJdbcTemplate.queryForList(sql);
		for(int i = 0; i < list.size(); i++){
			map = list.get(i);
			map.put("id", i+1);
			list.set(i, map);
		}
		sr.setResult(list);
		return sr;
	}

	@Override
	public ServiceResult branchProductList() {
		ServiceResult sr = new ServiceResult();
		List<Map<String, Object>> list = null;
		Map<String, Object> map = null;
		String sql = " select a.productName2,a.branchCode,b.branchName,a.count2 from "
				+ " (select a.PRODUCT_NAME productName2,a.BRANCH_CODE branchCode,count(*) count2 from tb_sp_order a "
				+ " where a.STATUS = '1' GROUP BY a.BRANCH_CODE,a.PRODUCT_NAME) a, "
				+ " (select b.BRANCH_CODE branchCode1,b.BRANCH_NAME branchName from tb_sys_hotarea b GROUP BY b.BRANCH_CODE) b "
				+ " where a.branchCode = b.branchCode1 GROUP BY a.count2 desc ";
		list = readJdbcTemplate.queryForList(sql);
		for(int i = 0; i < list.size(); i++){
			map = list.get(i);
			map.put("id", i+1);
			list.set(i, map);
		}
		sr.setResult(list);
		return sr;
	}

	@Override
	public ServiceResult myProductCountList(int pageNumber, int pageSize) {
		ServiceResult sr = new ServiceResult();
		List<Map<String, Object>> list = null;
		List<Map<String, Object>> list2 = null;
		Map<String, Object> map = new HashMap<String, Object>();
		
		String sql2 = " select a.USER_ID userId, b.USER_NAME userName, "
				+ " a.PRODUCT_NAME productName, count(*) count "
				+ " from tb_sp_order a ,tb_sp_user b "
				+ " where a.USER_ID = b.USER_CODE and a.Policy_No <> '' and a.Policy_No <> 'null' "
				+ " GROUP BY a.USER_ID,a.PRODUCT_NAME ORDER BY a.USER_ID ";
		list2 = readJdbcTemplate.queryForList(sql2);
		
		String sql = " select a.USER_ID userId, b.USER_NAME userName, "
				+ " a.PRODUCT_NAME productName, count(*) count "
				+ " from tb_sp_order a ,tb_sp_user b "
				+ " where a.USER_ID = b.USER_CODE and a.Policy_No <> '' and a.Policy_No <> 'null' "
				+ " GROUP BY a.USER_ID,a.PRODUCT_NAME ORDER BY a.USER_ID,count DESC "
				+ " LIMIT ? , ? ";
		list = readJdbcTemplate.queryForList(sql, new Object[]{(pageNumber - 1) * pageSize, pageNumber * pageSize});
		
		map.put("total", list2.size());
		map.put("list", list);
		sr.setResult(map);
		return sr;
	}

}
